<?php


namespace lib;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;


trait Excel
{
    /**
     * 导入数据(xlsx,xls,csv)
     * @param string $filepath 文件路径
     * @param array $filedList 字段
     * @param bool $isCutting 是否切割数组
     * @param int $size 切割数
     * @return array $data
     */
    public static function import($filepath, $filedList = [], $isCutting = false, $size = 2000)
    {
        $ext = pathinfo($filepath, PATHINFO_EXTENSION);
        $reader = IOFactory::createReader(ucfirst($ext));
        $reader->setReadDataOnly(TRUE);
        $objPHPExcel = $reader->load($filepath); //载入excel表格

        $worksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $worksheet->getHighestRow();//取得总行数
        $highestColumn = $worksheet->getHighestColumn(); // 取得总列数
        $cellList = [];
        foreach ($filedList as $k => $v) $cellList[] = self::IntToChr($k);

        //if ($cellList[count($filedList) - 1] != $highestColumn) return [];
        $data = [];
        for ($i = 0; $i < $highestRow - 1; $i++) {
            for ($j = 0; $j < count($cellList); $j++) {
                $data[$i][$filedList[$j]] = $worksheet->getCellByColumnAndRow($j + 1, $i + 2)->getValue();
                if (is_object($data[$i][$filedList[$j]])) $data[$i][$filedList[$j]] = $data[$i][$filedList[$j]]->__toString();
                $data[$i][$filedList[$j]] = preg_replace("/(\s|\ \;|　|\xc2\xa0)/", "", $data[$i][$filedList[$j]]);
            }
        }
        if ($isCutting) return array_chunk($data, $size);
        return $data;
    }

    /**
     * 导出数据(xlsx,xls,csv)
     * @param array $data 导出数据
     * @param array $filedList 字段
     * @param array $title 列表头
     * @param string $filename 导出文件名
     * @param string $ext 文件后缀名
     */
    public static function export($data = [], $filedList = [], $title = [], $filename = '导出数据', $ext = 'xlsx')
    {
        $filename = $filename . '-' . date('YmdHis');
        $filetitle = iconv('UTF-8', 'GBK', $filename);
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        array_unshift($data, $title);
        $i = 1;
        foreach ($data as $raw => $key) {
            foreach ($filedList as $k => $v) {
                if (is_numeric($key[$v]) && $key[$v] > 1000000) {
                    $worksheet->setCellValueExplicitByColumnAndRow($k + 1, $raw + 1, $key[$v], DataType::TYPE_STRING);
                } else {
                    $worksheet->setCellValueByColumnAndRow($k + 1, $raw + 1, $key[$v]);
                }
            }
            $i++;
        }
        if ($ext == 'xls') {
            header('Content-Type: application/vnd.ms-excel');
        } else {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        }
        header('Content-Disposition: attachment;filename="' . $filetitle . '"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, $ext);
        $writer->save('php://output');
        exit;
    }

    /**
     * 保存数据(xlsx,xls,csv)
     * @param array $data
     * @param array $filedList
     * @param array $title
     * @param string $filename
     * @param string $ext
     */
    public static function save($data = [], $filedList = [], $title = [], $filename = '导出数据', $ext = 'xlsx')
    {
        $filetitle = md5($filename);
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        array_unshift($data, $title);
        //单元格内容水平居中
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];
        foreach ($data as $raw => $key) {
            foreach ($filedList as $k => $v) {
                $worksheet->getStyleByColumnAndRow($k + 1, $raw + 1)->applyFromArray($styleArray);
                if (is_numeric($key[$v]) && $key[$v] > 1000000) {
                    $worksheet->setCellValueExplicitByColumnAndRow($k + 1, $raw + 1, $key[$v], DataType::TYPE_STRING);
                } else {
                    $worksheet->setCellValueByColumnAndRow($k + 1, $raw + 1, $key[$v]);
                }
            }
        }
        //保存
        $xlsx_url = public_path('xlsx') . date('Ymd');
        if (!is_dir($xlsx_url)) {
            self::makeDir($xlsx_url);
        }
        $writer = IOFactory::createWriter($spreadsheet, ucfirst($ext));
        $writer->save($xlsx_url . '/' . $filetitle . '.' . $ext);
    }


    /**
     * 在模板里面保存数据(xlsx,xls,csv)
     * @param array $data
     * @param array $filedList
     * @param string $template 模板地址或者名称
     * @param string $filename
     * @param string $ext
     */
    public static function saveTemplate($data = [], $filedList = [], $template = '', $filename = '导出数据', $ext = 'xlsx')
    {

        $template_file_path = public_path('xlsx/template') . $template . '.' . $ext;
        if (!is_file($template_file_path)) {
            exception('导出模板不存在！');
        }
        $spreadsheet = IOFactory::load($template_file_path);
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow = $worksheet->getHighestRow(); // 总行数
        //单元格内容水平居中
        $styleArray = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
            ],
        ];
        foreach ($data as $raw => $key) {
            foreach ($filedList as $k => $v) {
                $worksheet->getStyleByColumnAndRow($k + 1, $highestRow + $raw + 1)->applyFromArray($styleArray);
                if (is_numeric($key[$v]) && $key[$v] > 1000000) {
                    $worksheet->getCellByColumnAndRow($k + 1, $highestRow + $raw + 1)->setValueExplicit($key[$v], DataType::TYPE_STRING);
                } else {
                    $worksheet->getCellByColumnAndRow($k + 1, $highestRow + $raw + 1)->setValue($key[$v]);
                }
            }
        }
        //保存
        $xlsx_url = public_path('xlsx/export') . date('Ymd');
        if (!is_dir($xlsx_url)) {
            self::makeDir($xlsx_url);
        }
        $writer = IOFactory::createWriter($spreadsheet, ucfirst($ext));
        $save_path = $xlsx_url . '/' . $filename . '.' . $ext;
        $writer->save($save_path);
        return str_replace(public_path(), "", $save_path);
    }

    /**
     * 生成Excel列标
     * @param int $index 索引值
     * @param int $start 字母起始值
     * @return string 返回字母
     */
    protected static function IntToChr($index, $start = 65)
    {
        $str = '';
        if (floor($index / 26) > 0) {
            $str .= self::IntToChr(floor($index / 26) - 1);
        }
        return $str . chr($index % 26 + $start);
    }


    /**
     * 创建多级目录
     * @param $dir
     * @return bool
     */
    protected static function makeDir($dir)
    {
        return is_dir($dir) or self::makeDir(dirname($dir)) and mkdir($dir);
    }

    /**
     * Excel列标(A~BZ)
     * @return array 返回字母
     */
    protected static function getCellName()
    {
        $cellName = [];
        $startL = 'A';
        while ($startL != 'CA') $cellName[] = $startL++;
        return $cellName;
    }
}